IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[JobFailureNotificationLastInstance]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[JobFailureNotificationLastInstance](
	[last_instance_id] [int] NULL
) ON [PRIMARY]
END
GO
CREATE procedure dbo.JobFailureNotification_sp
as

declare	@instance_id		int ,
	@last_instance_id	int ,
	@new_last_instance_id	int ,
	@job_name		varchar(8000) ,
	@message		varchar(8000) ,
	@run_date		varchar(10) ,
	@run_time		varchar(10) ,
	@MySubject		varchar(8000) ,
	@MyMessage	varchar(8000),
	@MostRecent		varchar(8000)
	
select @last_instance_id = max( last_instance_id ) from JobFailureNotificationLastInstance

declare	FailuresCSR cursor for
select	jh.instance_id ,
	j.name	as	job_name ,
	jh.message ,
	jh.run_date ,
	jh.run_time,
	( ------------------------------------------------------------------------------------------------------------------------------------
	select	message
	from	msdb..sysjobhistory
	where	instance_id	=	( -- gives the most recent instance ----------------------------------------------------------------------------------------------
					select	max(instance_id)
					from	msdb..sysjobhistory
					where	job_id	=	jh.job_id 
					and	step_id	<>	0
					) ------------------------------------------------------------------------------------------------------------------------------------
	) ------------------------------------------------------------------------------------------------------------------------------------
	asMostRecent

from	msdb..sysjobs			j
	inner join msdb..sysjobhistory	jh	on	jh.job_id	=	j.job_id
where	instance_id	>	@last_instance_id
and	run_status	=	0
and	step_id	=	0

-- print 'entering loop'
open FailuresCSR
fetch next from FailuresCSR into @instance_id , @job_name , @Message , @run_date , @run_time, @MostRecent
while @@fetch_status = 0
	begin	set @MySubject = @@servername + ' : ' + @job_name -- gives the server and the Job in the subject line of the email
		set @MyMessage = @Message +  
			char(13) + char(10) + char(13) + char(10) + 'Date:  ' + 
			substring(  convert( varchar(8) , convert( varchar(8) ,@run_date ) ) , 1 , 4 ) + '-' +   -- 'yyyy-'                 -- conversion from integer to varchar
			substring( convert( varchar(8) , convert( varchar(8) ,@run_date ) ) , 5 , 2 ) + '-' +   -- 'mm-'
			substring( convert( varchar(8) , convert( varchar(8) ,@run_date ) ) , 7 , 2 ) + ' ' +   -- 'dd '
			char(13) + char(10) + char(13) + char(10) +
			 'Time Failed:  ' + 
			case	when substring ( right ('000000' +  substring ( convert(  varchar(6) , @run_time ) , 1 , 6 ), 6) , 1 , 2 )>12	then
					convert( varchar(6) , substring ( convert(  varchar(6) , @run_time ) , 1 , 2 ) -12 )	
				else	substring ( right ('000000' +  substring ( convert(  varchar(6) , @run_time ) , 1 , 6 ), 6) , 1 , 2 )--hh
			end + ':' +
			substring (right  ('000000' +  substring ( convert(  varchar(6) , @run_time ) , 1 , 6 ), 6) , 3 , 2 )+':'  + --mm -- conversion from INT to VARCHAR./ convert out of military time
			substring (right  ('000000' + substring ( convert(  varchar(6) , @run_time ) , 1 , 6 ), 6) , 5 , 2 )	+        --ss
			case 	when  substring ( right ('000000' +  substring ( convert(  varchar(6) , @run_time ) , 1 , 6 ), 6) , 1 , 2 )>12 	then	' PM'
				else														' AM'
			end + char(13) + char(10) + char(13) + char(10) + 
			'The Following is the output of the last step ran for the most recent execution of this job.  As long as the job notification procedure ran before that job was restarted it is accurate.'+
			char(13) + char(10) + char(13) + char(10) + 
			@MostRecent + char (13) +char(10)

		-- print 'inside loop'
		if  @instance_id > @last_instance_id
			set @new_last_instance_id = @instance_id
	
--		exec master..xp_smtp_sendmail
--			@server		=	'mail.domain.com' ,
--			@from		=	'dbamail@domain.com' ,
--			@to			=	'dbas@domain.com' ,
--			@subject	=	@MySubject ,
--			@message	=	@MyMessage
		exec msdb.dbo.sp_send_dbmail 
			@profile_name = 'dbamail@domain.com' , 
			@recipients = 'dbas@domain.com' , 
			@subject = @MySubject , 
			@body = @MyMessage

		-- print @MyMessage
		fetch next from FailuresCSR into @instance_id , @job_name , @Message , @run_date , @run_time, @MostRecent
	end

close FailuresCSR
deallocate FailuresCSR

if isnumeric( @new_last_instance_id ) = 1
	update JobFailureNotificationLastInstance set last_instance_id = @new_last_instance_id    -- new last instance is updated
GO